package com.tjetc.dao.impl;

import com.tjetc.dao.BedDao;
import com.tjetc.domain.Bed;
import com.tjetc.utils.Db;
import com.tjetc.utils.PageInfo;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class BedDaoImpl implements BedDao {

    @Override
    public List<Bed> findByRid(int id) throws SQLException {
        return Db.runner0().query("select * from bed where roomid=?",new BeanListHandler<>(Bed.class),id);

    }

    @Override
    public List<Bed> findByDepid(int depid) throws SQLException {
        return Db.runner0().query("select * from bed where depid = ? and status = 0",new BeanListHandler<>(Bed.class),depid);
    }

    @Override
    public Bed findByUid(int uid) throws SQLException {
        return Db.runner0().query("select * from bed where uid = ?",new BeanHandler<>(Bed.class),uid);
    }

    @Override
    public int getCount(String number) throws SQLException {
        return ((Long)Db.runner0().query("SELECT COUNT(bed.id) \n" +
                "FROM bed\n" +
                "JOIN room ON bed.roomid = room.id\n" +
                "WHERE room.number LIKE ?",new ScalarHandler<>(),"%"+number+"%")).intValue();
    }

    @Override
    public int getCount1(int status, String number) throws SQLException {
        return ((Long)Db.runner0().query("SELECT COUNT(bed.id) \n" +
                "FROM bed\n" +
                "JOIN room ON bed.roomid = room.id\n" +
                "WHERE bed.`status`=? and room.number LIKE ? ",new ScalarHandler<>(),status,"%"+number+"%")).intValue();
    }

    @Override
    public List<Bed> list(String number, PageInfo<Bed> pageInfo) throws SQLException {
        return Db.runner0().query("SELECT bed.* \n" +
                "FROM bed\n" +
                "JOIN room ON bed.roomid = room.id\n" +
                "WHERE room.number LIKE ? limit ?,?",new BeanListHandler<>(Bed.class),
                "%"+number+"%",pageInfo.getStartRecord(),pageInfo.getPageSize());
    }

    @Override
    public List<Bed> list1(int status, String number, PageInfo<Bed> pageInfo) throws SQLException {
        return Db.runner0().query("SELECT bed.* \n" +
                        "FROM bed\n" +
                        "JOIN room ON bed.roomid = room.id\n" +
                        "WHERE bed.`status`=? and  room.number LIKE ? limit ?,?",new BeanListHandler<>(Bed.class),
                status,"%"+number+"%",pageInfo.getStartRecord(),pageInfo.getPageSize());
    }

    @Override
    public int getRCount(int roomid) throws SQLException {
        return ((Long)Db.runner0().query("select count(id) from bed where roomid=?",new ScalarHandler<>(),
                roomid)).intValue();
    }

    @Override
    public Bed findByNumber(String number,int roomid) throws SQLException {
        return Db.runner0().query("select * from bed where number=? and roomid=?",
                new BeanHandler<>(Bed.class),number,roomid);
    }

    @Override
    public int add(Bed bed) throws SQLException {
        return Db.runner().update(Db.getConnection(),"insert into bed(number,depid,price,roomid) values(?,?,?,?)",
                bed.getNumber(),bed.getDepid(),bed.getPrice(),bed.getRoomid());
    }

    @Override
    public int update(Bed bed) throws SQLException {
        return Db.runner0().update("update bed set number=?,price=? where id=?",
                bed.getNumber(),bed.getPrice(),bed.getId());
    }

    @Override
    public Bed findById(int id) throws SQLException {
        return Db.runner0().query("select * from bed where id=?",
                new BeanHandler<>(Bed.class),id);
    }

    @Override
    public int del(int id) throws SQLException {
        return Db.runner().update(Db.getConnection(),"delete from bed where id=?",id);
    }

    @Override
    public int updatePrice(Bed bed) throws SQLException {
        return Db.runner0().update("update bed set price=? where roomid=?",bed.getPrice(),bed.getRoomid());
    }

    @Override
    public void updateStatus(Bed bed) throws SQLException {
        Db.runner().update(Db.getConnection(),"update bed set status=?,uid=? where id = ?",
                bed.getStatus(),bed.getUid(),bed.getId());
    }

    @Override
    public void userOut(int uid) throws SQLException {
        Db.runner().update(Db.getConnection(),"update bed set status = 0,uid = 0 where uid = ?",uid);
    }


}
